package jnpf.database.sql.enums;

import jnpf.database.constant.DbAliasConst;
import jnpf.database.enums.DbAliasEnum;
import jnpf.database.enums.ParamEnum;
import jnpf.database.source.DbBase;
import jnpf.database.sql.enums.base.SqlFrameBase;
import jnpf.database.sql.model.DbStruct;
import jnpf.util.StringUtil;
import lombok.AllArgsConstructor;
import lombok.Getter;

import java.util.List;

/**
 * 达梦 SQL语句模板
 *
 * @author JNPF开发平台组 YanYu
 * @version V3.3
 * @copyright 引迈信息技术有限公司
 * @date 2022/3/17
 */
@Getter
@AllArgsConstructor
public enum SqlDMEnum implements SqlFrameBase{

    /* =============================== 系统语句 ==================================== */
    FIELDS("select distinct A.column_name AS "+ DbAliasConst.FIELD + "," +
            " A.data_type AS "+DbAliasConst.DATA_TYPE+", " +
            "A.data_length AS "+DbAliasConst.DATA_LENGTH+"," +
            "case A.nullable when 'N' then '1' else '0' end AS "+DbAliasConst.PRIMARY_KEY+"," +
            "case A.nullable when 'N' then '0' else '1' end AS "+ DbAliasEnum.ALLOW_NULL.AS() +"," +
            "B.comments AS "+DbAliasConst.FIELD_COMMENT+" from all_tab_columns A " +
            "left join user_col_comments B on " +
            "A.table_name=B.table_name AND A.column_name=B.column_name AND A.OWNER = B.OWNER " +
            "where A.table_name = "+ ParamEnum.TABLE.getParamSign() +" AND A.OWNER = " + ParamEnum.DB_SCHEMA.getParamSign()
    ){
        @Override
        public void setStructParams(String table, DbStruct dbStruct, List<String> list) {
            list.add(table);
            list.add(dbStruct.getDmDbSchema());
        }
    },
    TABLES(
            // 作用:DBMS_STATS.GATHER_TABLE_STATS统计表,列,索引的统计信息（默认参数下是对表进行直方图信息收集，
            // 包含该表的自身-表的行数、数据块数、行长等信息；列的分析--列值的重复数、列上的空值、数据在列上的分布情况；
            // 索引的分析-索引页块的数量、索引的深度、索引聚合因子）.
//            "dbms_stats.GATHER_SCHEMA_stats (" + ParamEnum.DB_SCHEMA.getParamSign() +");\n" +
            "SELECT\n" +
            "ut.TABLE_NAME " + DbAliasConst.TABLE_NAME + ",utc.COMMENTS " + DbAliasConst.TABLE_COMMENT + "\n" +
            ",ut.num_rows " + DbAliasConst.TABLE_SUM + "\n" +
            "FROM ALL_TABLES AS ut\n" +
            "LEFT JOIN\n" +
            "all_tab_comments AS utc\n" +
            "ON\n" +
            "ut.TABLE_NAME = utc.TABLE_NAME AND ut.OWNER = utc.OWNER\n" +
            "WHERE ut.OWNER = " + ParamEnum.DB_SCHEMA.getParamSign() + "\n" +
            "ORDER BY F_TABLE_NAME;"
    ){
        @Override
        public void setStructParams(String table, DbStruct dbStruct, List<String> list) {
            list.add(dbStruct.getDmDbSchema());
        }
    },
    TABLE(
            TABLES.sqlFrame.replace("ORDER BY", "AND ut.TABLE_NAME = " + ParamEnum.TABLE.getParamSign() + " ORDER BY")
    ){
        @Override
        public void setStructParams(String table, DbStruct dbStruct, List<String> list) {
            list.add(dbStruct.getDmDbSchema());
            list.add(dbStruct.getDmDbSchema());
            list.add(table);
        }
    },
    EXISTS_TABLE(
            "SELECT COUNT (*) AS TOTAL FROM (" +
            "SELECT\n" +
            "ut.TABLE_NAME " + DbAliasConst.TABLE_NAME + " FROM ALL_TABLES AS ut\n" +
            "WHERE ut.OWNER = " + ParamEnum.DB_SCHEMA.getParamSign() +
            " and ut.TABLE_NAME = "+ParamEnum.TABLE.getParamSign()
            + ") AS COUNT_TAB"
    ){
        @Override
        public void setStructParams(String table, DbStruct dbStruct, List<String> list) {
            list.add(dbStruct.getDmDbSchema());
            list.add(table);
        }
    },


    /* =============================== 定义语句 ==================================== */
    // （Data Definition Language）简称 DDL：用来建立数据库、数据库对象和定义列的命令。包括：create、alter、drop
    /*=============================== ALTER ====================================*/
    CREATE_TABLE    ("<CREATE> <TABLE> {table} <(>\n" +
            "【{column} {dataType} [IDENTITY] [[NOT] [NULL]] [<DEFAULT> {defaultValue}] [<PRIMARY KEY>]】" +
            "<)> "){
        @Override
        public String sqlSpecial(String sqlFrame, List<String> frameParamList, List<String> paramList) {
            // 关于自增的处理
            String autoInc = paramList.get(frameParamList.indexOf("[AUTO_INCREMENT]"));
            if(StringUtil.isNotEmpty(autoInc)){
                sqlFrame = sqlFrame.replace("[[NOT] [NULL]]", "")
                        .replace("[<DEFAULT> {defaultValue}]", "");
            }else {
                sqlFrame = sqlFrame.replace("[IDENTITY]", "");
            }
            return sqlFrame;
        }

    },
    ALTER_DROP("ALTER TABLE 《schema》.{table} DROP COLUMN {column}"),
    /**
     * 添加字段
     */
    ALTER_ADD("ALTER TABLE 《schema》.{table} ADD {column} {dataType}"),
    /**
     * 修改字段
     */
    ALTER_TYPE("ALTER TABLE 《schema》.{table} <MODIFY> {column} {dataType}"),
    /**
     * 修改表名
     */
    ALTER_TABLE_NAME("ALTER TABLE 《schema》.{table} ALTER column {oldColumn} RENAME TO {newColumn};"),


    /* =============================== DML操作语句 ==================================== */
    // （Data Manipulation Language）简称 DML：用来操纵数据库中数据的命令。包括：select、insert、update、delete。

    DB_TIME_SQL     ("select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as TIME "),
    INSERT(
            "INSERT INTO 《schema》.{table} (【{column},】) VALUES (【{value},】)"
    ),
    /**
     *
     */
    CREATE_SCHEMA(
            "CREATE SCHEMA \"{schema}\";"
    ),
    DROP_SCHEMA(
            "DROP SCHEMA \"{schema}\" RESTRICT"
    ),
    DROP_TABLE(
            SqlPostgreSQLEnum.DROP_TABLE
            ),
    COMMENT_COLUMN(
            SqlPostgreSQLEnum.COMMENT_COLUMN
            ),
    COMMENT_TABLE(
            SqlPostgreSQLEnum.COMMENT_TABLE
            ),
    CREATE(
            SqlPostgreSQLEnum.CREATE
            ),



    /*=============================== 其他 ====================================*/

    ;

    private final String dbEncode = DbBase.DM;
    private final String sqlFrame;

    SqlDMEnum(SqlFrameBase sqlEnum){
        this.sqlFrame = sqlEnum.getSqlFrame();
    }

}
